

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=&#34;auto&#34;>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.png">
  <link rel="icon" type="image/png" href="/img/favicon.png">
  <meta name="viewport"
        content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="CodeHope">
  <meta name="author" content="CodeHope">
  <meta name="keywords" content="希望">
  <title>ORM (Flask-Sqlalchemy)-基础版 - CodeHope</title>

  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/github-markdown-css@4.0.0/github-markdown.min.css" />
  <link  rel="stylesheet" href="/lib/hint/hint.min.css" />

  
    
    
      
      
        
          
          
          
        
        <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/prismjs@1.22.0/themes/prism-tomorrow.min.css" />
      
      
        <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/prismjs@1.22.0/plugins/line-numbers/prism-line-numbers.min.css" />
      
    
  

  
    <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.5.7/dist/jquery.fancybox.min.css" />
  



<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_ba1fz6golrf.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_kmeydafke9r.css">


<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script id="fluid-configs">
    var Fluid = window.Fluid || {};
    var CONFIG = {"hostname":"quancundexiwang.wang","root":"/","version":"1.8.7","typing":{"enable":true,"typeSpeed":70,"cursorChar":"_","loop":false},"anchorjs":{"enable":true,"element":"h1,h2,h3,h4,h5,h6","placement":"left","visible":"hover","icon":""},"progressbar":{"enable":true,"height_px":3,"color":"#29d","options":{"showSpinner":true,"trickleSpeed":100}},"copy_btn":true,"image_zoom":{"enable":true},"toc":{"enable":true,"headingSelector":"h1,h2,h3,h4,h5,h6","collapseDepth":0},"lazyload":{"enable":true,"onlypost":true},"web_analytics":{"enable":false,"baidu":null,"google":null,"gtag":null,"tencent":{"sid":null,"cid":null},"woyaola":null,"cnzz":null,"leancloud":{"app_id":"Mi65hxq7VAFUDwOLeIGAOgiV-gzGzoHsz","app_key":"hMuhiD4FRqhns4giqLiEH9HG","server_url":null}}};
  </script>
  <script  src="/js/utils.js" ></script>
  <script  src="/js/color-schema.js" ></script>
<meta name="generator" content="Hexo 5.3.0"></head>


<body>
  <header style="height: 80vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand"
       href="/">&nbsp;<strong>CodeHope</strong>&nbsp;</a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                村头
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                我
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" data-toggle="modal" data-target="#modalSearch">&nbsp;<i
                class="iconfont icon-search"></i>&nbsp;</a>
          </li>
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" href="javascript:">&nbsp;<i
                class="iconfont icon-dark" id="color-toggle-icon"></i>&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="banner" id="banner" parallax=true
         style="background: url('https://ss1.bdstatic.com/70cFvXSh_Q1YnxGkpoWK1HF6hhy/it/u=351499770,1971798553&fm=26&gp=0.jpg') repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="page-header text-center fade-in-up">
            <span class="h2" id="subtitle" title="ORM (Flask-Sqlalchemy)-基础版">
              
            </span>

            
              <div class="mt-3">
  
  
    <span class="post-meta">
      <i class="iconfont icon-date-fill" aria-hidden="true"></i>
      <time datetime="2020-12-14 22:30" pubdate>
        2020年12月14日 晚上
      </time>
    </span>
  
</div>

<div class="mt-1">
  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      2.8k 字
    </span>
  

  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      42
       分钟
    </span>
  

  
  
</div>

            
          </div>

          
            <div class="scroll-down-bar">
              <i class="iconfont icon-arrowdown"></i>
            </div>
          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid nopadding-x">
  <div class="row nomargin-x">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-x-md">
      <div class="container nopadding-x-md" id="board-ctn">
        <div class="py-5" id="board">
          <article class="post-content mx-auto">
            <!-- SEO header -->
            <h1 style="display: none">ORM (Flask-Sqlalchemy)-基础版</h1>
            
            <div class="markdown-body">
              <h1 id="ORM-flask-sqlalchemy-基础版"><a href="#ORM-flask-sqlalchemy-基础版" class="headerlink" title="ORM (flask-sqlalchemy)-基础版"></a>ORM (flask-sqlalchemy)-基础版</h1><blockquote>
<p>ORM (Object Relational Mapping) 对象关系映射</p>
</blockquote>
<blockquote>
<p>Web 开发中，一个重要的组成部分便是数据库了。Web 程序中最常用的莫过于关系型数据库了，也称 SQL 数据库</p>
<p>文档数据库（如 mongodb）、键值对数据库（如 redis）近几年也逐渐在 web 开发中流行起来，我们习惯把这两种数据库称为 NoSQL 数据库。</p>
</blockquote>
<blockquote>
<p>大多数的关系型数据库引擎（比如 MySQL、Postgres 和 SQLite）都有对应的 Python 包。在这里，我们不直接使用这些数据库引擎提供的 Python 包，而是使用对象关系映射（Object-Relational Mapper, ORM）框架</p>
<p>它将低层的数据库操作指令抽象成高层的面向对象操作。也就是说，如果我们直接使用数据库引擎，我们就要写 SQL 操作语句，但是，如果我们使用了 ORM 框架，我们对诸如表、文档此类的数据库实体就可以简化成对 Python 对象的操作。</p>
</blockquote>
<ul>
<li><p>面向对象的编程思想，方便扩充</p>
</li>
<li><p>少些原生SQL语句，提升开发效率</p>
</li>
<li><p>支持多种类型数据库，方便切换</p>
</li>
</ul>
<h2 id="1flask-sqlalchemy介绍及安装"><a href="#1flask-sqlalchemy介绍及安装" class="headerlink" title="1flask-sqlalchemy介绍及安装"></a>1<code>flask-sqlalchemy</code>介绍及安装</h2><blockquote>
<p>Python 中最广泛使用的 ORM 框架是 SQLAlchemy，它是一个很强大的关系型数据库框架，不仅支持高层的 ORM，也支持使用低层的 SQL 操作，另外，它也支持多种数据库引擎，如 MySQL、Postgres 和 SQLite 等。</p>
</blockquote>
<p><code>安装：flask-sqlalchemy</code></p>
<div class="code-wrapper"><pre class="line-numbers language-shell" data-language="shell"><code class="language-shell">▶ pip install flask-sqlalchemy<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p><code>安装：mysqlclient</code></p>
<div class="code-wrapper"><pre class="line-numbers language-shell" data-language="shell"><code class="language-shell">▶ pip install mysqlclient<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>


<h2 id="2-配置flask-sqlalchemy"><a href="#2-配置flask-sqlalchemy" class="headerlink" title="2.配置flask-sqlalchemy"></a>2.配置<code>flask-sqlalchemy</code></h2><blockquote>
<p>在 Flask-SQLAlchemy 中，数据库使用 URL 指定，下表列出了常见的数据库引擎和对应的 URL。</p>
</blockquote>
<table>
<thead>
<tr>
<th>数据库引擎</th>
<th>URL</th>
</tr>
</thead>
<tbody><tr>
<td>MySQL</td>
<td><code>mysql://username:password@hostname/database</code></td>
</tr>
<tr>
<td>Postgres</td>
<td><code>postgresql://username:password@hostname/database</code></td>
</tr>
<tr>
<td>SQLite (Unix)</td>
<td><code>sqlite:////absolute/path/to/database</code></td>
</tr>
<tr>
<td>SQLite (Windows)</td>
<td><code>sqlite:///c:/absolute/path/to/database</code></td>
</tr>
</tbody></table>
<blockquote>
<p>上面的表格中，<code>username 和 password 表示登录数据库的用户名和密码</code>，<code>hostname 表示 SQL 服务所在的主机</code>，可以是本地主机（localhost）也可以是远程服务器，<code>database 表示要使用的数据库</code>。有一点需要注意的是，SQLite 数据库不需要使用服务器，它使用硬盘上的文件名作为 database。</p>
</blockquote>
<h3 id="2-1-创建一个users表"><a href="#2-1-创建一个users表" class="headerlink" title="2.1 创建一个users表"></a>2.1 <span style='color:red'>创建一个<code>users</code>表</span></h3><p>应用配置项介绍:</p>
<ul>
<li><code>SQLALCHEMY_DATABASE_URI</code> 指定了 <code>SQLAlchemy</code> 所要操作的数据库</li>
<li><code>SQLALCHEMY_TRACK_MODIFICATIONS</code> 设置是不是要跟踪修改(会消耗额外的内存)</li>
<li>定义的 <code>User</code> 模型必须继承自 <code>db.Model</code>，这里的模型其实就对应着数据库中的表。</li>
<li><code>__tablename__ </code>定义了在数据库中使用的表名，如果该变量没有被定义，<code>Flask-SQLAlchemy 会使用一个默认名字。</code></li>
</ul>
<div class="code-wrapper"><pre class="line-numbers language-python" data-language="python"><code class="language-python"><span class="token keyword">from</span> flask <span class="token keyword">import</span> Flask
<span class="token keyword">from</span> flask_sqlalchemy <span class="token keyword">import</span> SQLAlchemy  <span class="token comment"># 引入SQLAlchemy</span>

app <span class="token operator">=</span> Flask<span class="token punctuation">(</span>__name__<span class="token punctuation">)</span>  <span class="token comment"># 创建app</span>

<span class="token comment">#配置名称字符串常量化</span>
sql_database_uri <span class="token operator">=</span> <span class="token string">'SQLALCHEMY_DATABASE_URI'</span> 
sql_alchemy_track_modifications <span class="token operator">=</span> <span class="token string">'SQLALCHEMY_TRACK_MODIFICATIONS'</span>

app<span class="token punctuation">.</span>config<span class="token punctuation">[</span>sql_database_uri<span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token string">'mysql://root:12345678@localhost/ACCOUNT'</span>
app<span class="token punctuation">.</span>config<span class="token punctuation">[</span>sql_alchemy_track_modifications<span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token boolean">True</span>

db <span class="token operator">=</span> SQLAlchemy<span class="token punctuation">(</span>app<span class="token punctuation">)</span> <span class="token comment"># 注册数据库实例</span>



<span class="token keyword">class</span> <span class="token class-name">User</span><span class="token punctuation">(</span>db<span class="token punctuation">.</span>Model<span class="token punctuation">)</span><span class="token punctuation">:</span>
    __tablename__ <span class="token operator">=</span> <span class="token string">'user'</span> 
    
   <span class="token comment"># 定义数据表的column数据字段</span>
    <span class="token builtin">id</span> <span class="token operator">=</span> db<span class="token punctuation">.</span>Column<span class="token punctuation">(</span>db<span class="token punctuation">.</span>Integer<span class="token punctuation">,</span> primary_key<span class="token operator">=</span><span class="token boolean">True</span><span class="token punctuation">)</span>
    username <span class="token operator">=</span> db<span class="token punctuation">.</span>Column<span class="token punctuation">(</span>db<span class="token punctuation">.</span>String<span class="token punctuation">(</span><span class="token number">80</span><span class="token punctuation">)</span><span class="token punctuation">,</span> unique<span class="token operator">=</span><span class="token boolean">True</span><span class="token punctuation">)</span>
    password <span class="token operator">=</span> db<span class="token punctuation">.</span>Column<span class="token punctuation">(</span>db<span class="token punctuation">.</span>String<span class="token punctuation">(</span><span class="token number">256</span><span class="token punctuation">)</span><span class="token punctuation">,</span> unique<span class="token operator">=</span><span class="token boolean">True</span><span class="token punctuation">)</span>
    email <span class="token operator">=</span> db<span class="token punctuation">.</span>Column<span class="token punctuation">(</span>db<span class="token punctuation">.</span>String<span class="token punctuation">(</span><span class="token number">120</span><span class="token punctuation">)</span><span class="token punctuation">,</span> unique<span class="token operator">=</span><span class="token boolean">True</span><span class="token punctuation">)</span>
    age <span class="token operator">=</span> db<span class="token punctuation">.</span>Column<span class="token punctuation">(</span>db<span class="token punctuation">.</span>Integer<span class="token punctuation">,</span> unique<span class="token operator">=</span><span class="token boolean">False</span><span class="token punctuation">,</span> nullable<span class="token operator">=</span><span class="token boolean">True</span><span class="token punctuation">)</span>
    birthday <span class="token operator">=</span> db<span class="token punctuation">.</span>Column<span class="token punctuation">(</span>db<span class="token punctuation">.</span>Date<span class="token punctuation">,</span> unique<span class="token operator">=</span><span class="token boolean">False</span><span class="token punctuation">,</span> nullable<span class="token operator">=</span><span class="token boolean">True</span><span class="token punctuation">)</span>
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>执行创建表</p>
<blockquote>
<p>Python shell 中导入 db 对象并调用 SQLAlchemy 类的 create_all() 方法：</p>
</blockquote>
<div class="code-wrapper"><pre class="line-numbers language-powershell" data-language="powershell"><code class="language-powershell">Python 3<span class="token punctuation">.</span>8<span class="token punctuation">.</span>5 <span class="token punctuation">(</span>v3<span class="token punctuation">.</span>8<span class="token punctuation">.</span>5:580fbb018f<span class="token punctuation">,</span> Jul 20 2020<span class="token punctuation">,</span> 12:11:27<span class="token punctuation">)</span> 
<span class="token namespace">[Clang 6.0 (clang-600.0.57)]</span> on darwin
App: app <span class="token namespace">[production]</span>
Instance: <span class="token operator">/</span>Users<span class="token operator">/</span>codehope<span class="token operator">/</span>PycharmProjects<span class="token operator">/</span>flaskProject<span class="token operator">/</span>instance
 
<span class="token keyword">from</span> app import db
db<span class="token punctuation">.</span>create_all<span class="token punctuation">(</span><span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>我们验证一下，”users” 表是否创建成功：</p>
<div class="code-wrapper"><pre class="line-numbers language-shell" data-language="shell"><code class="language-shell">mysql&gt; use ACCOUNT;

Database changed
mysql&gt; show tables;

+-------------------+
| Tables_in_account |
+-------------------+
| user             |
+-------------------+
1 row in set (0.00 sec)<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>查看表结构</p>
<div class="code-wrapper"><pre class="line-numbers language-shell" data-language="shell"><code class="language-shell">mysql&gt; show columns from user;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| username | varchar(80)  | YES  | UNI | NULL    |                |
| password | varchar(256) | YES  | UNI | NULL    |                |
| email    | varchar(120) | YES  | UNI | NULL    |                |
| age      | int(11)      | YES  |     | NULL    |                |
| birthday | date         | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>到这里创建<code>user</code>表成功.</p>
<h3 id="2-2-向user表中插入数据"><a href="#2-2-向user表中插入数据" class="headerlink" title="2.2 向user表中插入数据"></a>2.2 <span style='color:red'>向<code>user</code>表中插入数据</span></h3><p>使用<code>db.session.add()</code>插入数据</p>
<div class="code-wrapper"><pre class="line-numbers language-python" data-language="python"><code class="language-python"><span class="token decorator annotation punctuation">@app<span class="token punctuation">.</span>route</span><span class="token punctuation">(</span><span class="token string">'/add_user'</span><span class="token punctuation">)</span>
<span class="token keyword">def</span> <span class="token function">add_user</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
    user1 <span class="token operator">=</span> User<span class="token punctuation">(</span><span class="token string">'ethan'</span><span class="token punctuation">,</span> <span class="token string">'1234567'</span><span class="token punctuation">,</span> <span class="token string">'ethan@example.com'</span><span class="token punctuation">,</span> <span class="token number">13</span><span class="token punctuation">,</span> <span class="token string">'2020-09-09'</span><span class="token punctuation">)</span>
    user2 <span class="token operator">=</span> User<span class="token punctuation">(</span><span class="token string">'jack'</span><span class="token punctuation">,</span> <span class="token string">'12334567'</span><span class="token punctuation">,</span> <span class="token string">'jack@example.com'</span><span class="token punctuation">,</span> <span class="token number">23</span><span class="token punctuation">,</span> <span class="token string">'2010-09-09'</span><span class="token punctuation">)</span>
    db<span class="token punctuation">.</span>session<span class="token punctuation">.</span>add<span class="token punctuation">(</span>user1<span class="token punctuation">)</span>
    db<span class="token punctuation">.</span>session<span class="token punctuation">.</span>add<span class="token punctuation">(</span>user2<span class="token punctuation">)</span>
    db<span class="token punctuation">.</span>session<span class="token punctuation">.</span>commit<span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token keyword">return</span> make_response<span class="token punctuation">(</span><span class="token punctuation">&#123;</span>
        <span class="token string">'msg'</span><span class="token punctuation">:</span> <span class="token string">'success'</span>
    <span class="token punctuation">&#125;</span><span class="token punctuation">,</span> <span class="token number">201</span><span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<blockquote>
<p>我们在将数据添加到会话后，在最后要记得调用 db.session.commit() 提交事务，这样，数据才会被写入到数据库。</p>
</blockquote>
<p>访问url<code>http://127.0.0.1:5000/add_user</code></p>
<p>返回：</p>
<div class="code-wrapper"><pre class="line-numbers language-json" data-language="json"><code class="language-json"><span class="token punctuation">&#123;</span>
	<span class="token property">"msg"</span><span class="token operator">:</span> <span class="token string">"success"</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<p>查看数据表</p>
<!DOCTYPE html>
<html>
  <head>
    <title></title>
    <meta charset="UTF-8">
  </head>
<body>
<table border="1" style="border-collapse:collapse">
<tr>
  <th>id</th>
  <th>username</th>
  <th>password</th>
  <th>email</th>
  <th>age</th>
  <th>birthday</th>
</tr>
<tr>
  <td>7</td>
  <td>ethan</td>
  <td>1234567</td>
  <td>ethan@example.com</td>
  <td>13</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>8</td>
  <td>jack</td>
  <td>12334567</td>
  <td>jack@example.com</td>
  <td>23</td>
  <td>2010-09-09</td>
</tr>
</table>
</body>
</html>

<p>写入成功！</p>
<h3 id="2-3-修改User表中的数据"><a href="#2-3-修改User表中的数据" class="headerlink" title="2.3 修改User表中的数据"></a>2.3 <span style='color:red'>修改<code>User</code>表中的数据</span></h3><div class="code-wrapper"><pre class="line-numbers language-python" data-language="python"><code class="language-python"><span class="token decorator annotation punctuation">@app<span class="token punctuation">.</span>route</span><span class="token punctuation">(</span><span class="token string">'/update_user'</span><span class="token punctuation">,</span> methods<span class="token operator">=</span><span class="token punctuation">[</span><span class="token string">'GET'</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
<span class="token keyword">def</span> <span class="token function">update_user</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
    <span class="token keyword">try</span><span class="token punctuation">:</span>
      	<span class="token comment"># 拿出请求的参数，如果`username`存在就继续，不存在就抛出错误`用户不存在`</span>
        username <span class="token operator">=</span> request<span class="token punctuation">.</span>args<span class="token punctuation">.</span>get<span class="token punctuation">(</span><span class="token string">'username'</span><span class="token punctuation">)</span>
        
        <span class="token comment"># 从数据库表中找到这个用户对应的ORM映射对象</span>
        user <span class="token operator">=</span> User<span class="token punctuation">.</span>query<span class="token punctuation">.</span>filter_by<span class="token punctuation">(</span>username<span class="token operator">=</span>username<span class="token punctuation">)</span><span class="token punctuation">.</span>first<span class="token punctuation">(</span><span class="token punctuation">)</span>
        
        <span class="token comment"># 对找出来的对象的`username`重新赋值</span>
        user<span class="token punctuation">.</span>username <span class="token operator">=</span> username <span class="token operator">+</span> <span class="token string">'v.1'</span>
        
        <span class="token comment"># 添加更新后的username映射对象到session (其实这里我试了没有添加到session提交也能修改成功😄)</span>
        db<span class="token punctuation">.</span>session<span class="token punctuation">.</span>add<span class="token punctuation">(</span>user<span class="token punctuation">)</span>
        
        <span class="token comment"># 提示会话事务，完成修改</span>
        db<span class="token punctuation">.</span>session<span class="token punctuation">.</span>commit<span class="token punctuation">(</span><span class="token punctuation">)</span>
        
        <span class="token keyword">return</span> make_response<span class="token punctuation">(</span><span class="token punctuation">&#123;</span>
            <span class="token string">'msg'</span><span class="token punctuation">:</span> <span class="token string">'success'</span>
        <span class="token punctuation">&#125;</span><span class="token punctuation">,</span> <span class="token number">201</span><span class="token punctuation">)</span>
    <span class="token keyword">except</span><span class="token punctuation">:</span>
        <span class="token keyword">return</span> make_response<span class="token punctuation">(</span><span class="token punctuation">&#123;</span>
            <span class="token string">'msg'</span><span class="token punctuation">:</span> <span class="token string">'用户不存在'</span>
        <span class="token punctuation">&#125;</span><span class="token punctuation">,</span> <span class="token number">404</span><span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<!DOCTYPE html>
<html>
  <head>
    <title></title>
    <meta charset="UTF-8">
  </head>
<body>
<table border="1" style="border-collapse:collapse">
<tr>
  <th>id</th>
  <th>username</th>
  <th>password</th>
  <th>email</th>
  <th>age</th>
  <th>birthday</th>
</tr>
<tr>
  <td>1</td>
  <td>ethan</td>
  <td>1234567</td>
  <td>ethan@example.com</td>
  <td>13</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>2</td>
  <td>jack</td>
  <td>12334567</td>
  <td>jack@example.com</td>
  <td>23</td>
  <td>2010-09-09</td>
</tr>
</table>
</body>
</html>

<p>测试访问 <code>http://localhost:5000/update_user?username=jack</code></p>
<div class="code-wrapper"><pre class="line-numbers language-json" data-language="json"><code class="language-json"><span class="token punctuation">&#123;</span>
	<span class="token property">"msg"</span><span class="token operator">:</span> <span class="token string">"success"</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<!DOCTYPE html>
<html>
  <head>
    <title></title>
    <meta charset="UTF-8">
  </head>
<body>
<table border="1" style="border-collapse:collapse">
<tr>
  <th>id</th>
  <th>username</th>
  <th>password</th>
  <th>email</th>
  <th>age</th>
  <th>birthday</th>
</tr>
<tr>
  <td>1</td>
  <td>ethan</td>
  <td>1234567</td>
  <td>ethan@example.com</td>
  <td>13</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>2</td>
  <td>jackv.1</td>
  <td>12334567</td>
  <td>jack@example.com</td>
  <td>23</td>
  <td>2010-09-09</td>
</tr>
</table>
</body>
</html>

<p>修改成功！</p>
<p>测试不存在<code>username</code>,修改过之后再次访问 <code>http://localhost:5000/update_user?username=jack</code></p>
<div class="code-wrapper"><pre class="line-numbers language-json" data-language="json"><code class="language-json"><span class="token punctuation">&#123;</span>
	<span class="token property">"msg"</span><span class="token operator">:</span> <span class="token string">"用户不存在"</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<h3 id="2-4-ORM删除User表数据"><a href="#2-4-ORM删除User表数据" class="headerlink" title="2.4 ORM删除User表数据"></a>2.4 ORM<span style='color:red'>删除<code>User表数据</code></span></h3><blockquote>
<p>删除数据，在数据库中有两种，物理删除和逻辑删除</p>
</blockquote>
<ul>
<li>物理删除是指从数据表中，将这个数据彻底删除</li>
<li>逻辑删除是指修改某个字段状态，区别于其他数据</li>
</ul>
<p><code>我们在这里例子的是物理删除的，因为逻辑删除就是更新字段，我们在上面已经学习过了</code></p>
<p><code> db.session.delete(user)</code></p>
<div class="code-wrapper"><pre class="line-numbers language-python" data-language="python"><code class="language-python"><span class="token decorator annotation punctuation">@app<span class="token punctuation">.</span>route</span><span class="token punctuation">(</span><span class="token string">'/delete_user'</span><span class="token punctuation">,</span> methods<span class="token operator">=</span><span class="token punctuation">[</span><span class="token string">'GET'</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
<span class="token keyword">def</span> <span class="token function">delete_user</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
    <span class="token keyword">try</span><span class="token punctuation">:</span>
        username <span class="token operator">=</span> request<span class="token punctuation">.</span>args<span class="token punctuation">.</span>get<span class="token punctuation">(</span><span class="token string">'username'</span><span class="token punctuation">)</span>
        user <span class="token operator">=</span> User<span class="token punctuation">.</span>query<span class="token punctuation">.</span>filter_by<span class="token punctuation">(</span>username<span class="token operator">=</span>username<span class="token punctuation">)</span><span class="token punctuation">.</span>first<span class="token punctuation">(</span><span class="token punctuation">)</span>
        db<span class="token punctuation">.</span>session<span class="token punctuation">.</span>delete<span class="token punctuation">(</span>user<span class="token punctuation">)</span>
        db<span class="token punctuation">.</span>session<span class="token punctuation">.</span>commit<span class="token punctuation">(</span><span class="token punctuation">)</span>
        <span class="token keyword">return</span> make_response<span class="token punctuation">(</span><span class="token punctuation">&#123;</span>
            <span class="token string">'msg'</span><span class="token punctuation">:</span> <span class="token string">'success'</span>
        <span class="token punctuation">&#125;</span><span class="token punctuation">,</span> <span class="token number">201</span><span class="token punctuation">)</span>
    <span class="token keyword">except</span><span class="token punctuation">:</span>
        <span class="token keyword">return</span> make_response<span class="token punctuation">(</span><span class="token punctuation">&#123;</span>
            <span class="token string">'msg'</span><span class="token punctuation">:</span> <span class="token string">'用户不存在'</span><span class="token punctuation">,</span>
            <span class="token string">'code'</span><span class="token punctuation">:</span> <span class="token number">404</span>
        <span class="token punctuation">&#125;</span><span class="token punctuation">,</span> <span class="token number">404</span><span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<!DOCTYPE html>
<html>
  <head>
    <title></title>
    <meta charset="UTF-8">
  </head>
<body>
<table border="1" style="border-collapse:collapse">
<tr>
  <th>id</th>
  <th>username</th>
  <th>password</th>
  <th>email</th>
  <th>age</th>
  <th>birthday</th>
</tr>
<tr>
  <td>1</td>
  <td>ethan</td>
  <td>1234567</td>
  <td>ethan@example.com</td>
  <td>13</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>2</td>
  <td>jackv.1v.1</td>
  <td>12334567</td>
  <td>jack@example.com</td>
  <td>23</td>
  <td>2010-09-09</td>
</tr>
</table>
</body>
</html>

<p>访问url:<code>http://127.0.0.1:5000/delete_user?username=jackv.1v.1</code></p>
<div class="code-wrapper"><pre class="line-numbers language-json" data-language="json"><code class="language-json"><span class="token punctuation">&#123;</span>
<span class="token property">"msg"</span><span class="token operator">:</span> <span class="token string">"success"</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<!DOCTYPE html>
<html>
  <head>
    <title></title>
    <meta charset="UTF-8">
  </head>
<body>
<table border="1" style="border-collapse:collapse">
<tr>
  <th>id</th>
  <th>username</th>
  <th>password</th>
  <th>email</th>
  <th>age</th>
  <th>birthday</th>
</tr>
<tr>
  <td>1</td>
  <td>ethan</td>
  <td>1234567</td>
  <td>ethan@example.com</td>
  <td>13</td>
  <td>2020-09-09</td>
</tr>
</table>
</body>
</html>

<h3 id="2-5-对数据库的查询和展示"><a href="#2-5-对数据库的查询和展示" class="headerlink" title="2.5 对数据库的查询和展示"></a>2.5 <span style='color:red'>对数据库的查询和展示</span></h3><h4 id="a-查询全部数据"><a href="#a-查询全部数据" class="headerlink" title="a. 查询全部数据"></a>a. 查询全部数据</h4><p><code>User.query.all()</code></p>
<p>首先我们疯狂写入数据</p>
<div class="code-wrapper"><pre class="line-numbers language-python" data-language="python"><code class="language-python"><span class="token decorator annotation punctuation">@app<span class="token punctuation">.</span>route</span><span class="token punctuation">(</span><span class="token string">'/add_user'</span><span class="token punctuation">)</span>
<span class="token keyword">def</span> <span class="token function">add_user</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
		<span class="token comment"># 随机生成username</span>
    randomUserName <span class="token operator">=</span> random<span class="token punctuation">.</span>choice<span class="token punctuation">(</span><span class="token string">'abcdefghijklmnopqrstuvwxyz!@#$%^&amp;*()'</span><span class="token punctuation">)</span>
    
    <span class="token comment"># 随机生成密码</span>
    randomPassword <span class="token operator">=</span> random<span class="token punctuation">.</span>randint<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">50</span><span class="token punctuation">)</span>
    
    <span class="token comment"># 随机生成年龄</span>
    randomAge <span class="token operator">=</span> random<span class="token punctuation">.</span>randint<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">50</span><span class="token punctuation">)</span>
    
    <span class="token comment"># 拼接email</span>
    randomEmail <span class="token operator">=</span> <span class="token string-interpolation"><span class="token string">f"</span><span class="token interpolation"><span class="token punctuation">&#123;</span>randomUserName<span class="token punctuation">&#125;</span></span><span class="token string">@example.com"</span></span>
    
    user <span class="token operator">=</span> User<span class="token punctuation">(</span>randomUserName<span class="token punctuation">,</span> randomPassword<span class="token punctuation">,</span> randomEmail<span class="token punctuation">,</span> randomAge<span class="token punctuation">,</span> <span class="token string">'2020-09-09'</span><span class="token punctuation">)</span>

    db<span class="token punctuation">.</span>session<span class="token punctuation">.</span>add<span class="token punctuation">(</span>user<span class="token punctuation">)</span>
    db<span class="token punctuation">.</span>session<span class="token punctuation">.</span>commit<span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token keyword">return</span> make_response<span class="token punctuation">(</span><span class="token punctuation">&#123;</span>
        <span class="token string">'msg'</span><span class="token punctuation">:</span> <span class="token string">'success'</span>
    <span class="token punctuation">&#125;</span><span class="token punctuation">,</span> <span class="token number">201</span><span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>然后把这个接口执行n次</p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201214183526.png" srcset="/img/loading.gif"></p>
<!DOCTYPE html>
<html>
  <head>
    <title></title>
    <meta charset="UTF-8">
  </head>
<body>
<table border="1" style="border-collapse:collapse">
<tr>
  <th>id</th>
  <th>username</th>
  <th>password</th>
  <th>email</th>
  <th>age</th>
  <th>birthday</th>
</tr>
<tr>
  <td>1</td>
  <td>ethan</td>
  <td>1234567</td>
  <td>ethan@example.com</td>
  <td>13</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>8</td>
  <td>i</td>
  <td>34</td>
  <td>i@example.com</td>
  <td>43</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>9</td>
  <td>d</td>
  <td>3</td>
  <td>d@example.com</td>
  <td>14</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>10</td>
  <td>r</td>
  <td>29</td>
  <td>r@example.com</td>
  <td>43</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>11</td>
  <td>m</td>
  <td>17</td>
  <td>m@example.com</td>
  <td>3</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>12</td>
  <td>w</td>
  <td>5</td>
  <td>w@example.com</td>
  <td>41</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>13</td>
  <td>^</td>
  <td>10</td>
  <td>^@example.com</td>
  <td>20</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>14</td>
  <td>o</td>
  <td>1</td>
  <td>o@example.com</td>
  <td>22</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>15</td>
  <td>!</td>
  <td>45</td>
  <td>!@example.com</td>
  <td>15</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>16</td>
  <td>p</td>
  <td>26</td>
  <td>p@example.com</td>
  <td>14</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>17</td>
  <td>a</td>
  <td>9</td>
  <td>a@example.com</td>
  <td>25</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>18</td>
  <td>%</td>
  <td>28</td>
  <td>%@example.com</td>
  <td>26</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>20</td>
  <td>q</td>
  <td>46</td>
  <td>q@example.com</td>
  <td>1</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>21</td>
  <td>h</td>
  <td>50</td>
  <td>h@example.com</td>
  <td>30</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>22</td>
  <td>(</td>
  <td>18</td>
  <td>(@example.com</td>
  <td>6</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>23</td>
  <td>y</td>
  <td>6</td>
  <td>y@example.com</td>
  <td>14</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>24</td>
  <td>#</td>
  <td>15</td>
  <td>#@example.com</td>
  <td>49</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>29</td>
  <td>s</td>
  <td>2</td>
  <td>s@example.com</td>
  <td>41</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>30</td>
  <td>&amp;</td>
  <td>13</td>
  <td>&amp;@example.com</td>
  <td>32</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>31</td>
  <td>k</td>
  <td>48</td>
  <td>k@example.com</td>
  <td>28</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>32</td>
  <td>)</td>
  <td>33</td>
  <td>)@example.com</td>
  <td>2</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>34</td>
  <td>f</td>
  <td>16</td>
  <td>[&#39;f&#39;]@example.com</td>
  <td>37</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>35</td>
  <td>u</td>
  <td>23</td>
  <td>u@example.com</td>
  <td>34</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>42</td>
  <td>t</td>
  <td>42</td>
  <td>t@example.com</td>
  <td>26</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>53</td>
  <td>z</td>
  <td>49</td>
  <td>z@example.com</td>
  <td>14</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>57</td>
  <td>b</td>
  <td>7</td>
  <td>b@example.com</td>
  <td>33</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>66</td>
  <td>e</td>
  <td>39</td>
  <td>e@example.com</td>
  <td>40</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>70</td>
  <td>j</td>
  <td>38</td>
  <td>j@example.com</td>
  <td>28</td>
  <td>2020-09-09</td>
</tr>
<tr>
  <td>71</td>
  <td>@</td>
  <td>35</td>
  <td>@@example.com</td>
  <td>23</td>
  <td>2020-09-09</td>
</tr>
</table>
</body>
</html>
<div class="code-wrapper"><pre class="line-numbers language-verilog" data-language="verilog"><code class="language-verilog"><span class="token punctuation">[</span>Clang <span class="token number">6.0</span> <span class="token punctuation">(</span>clang<span class="token operator">-</span><span class="token number">600.0</span><span class="token punctuation">.</span><span class="token number">57</span><span class="token punctuation">)</span><span class="token punctuation">]</span> on darwin
App<span class="token punctuation">:</span> app <span class="token punctuation">[</span>production<span class="token punctuation">]</span>
Instance<span class="token punctuation">:</span> <span class="token operator">/</span>Users<span class="token operator">/</span>codehope<span class="token operator">/</span>PycharmProjects<span class="token operator">/</span>flaskProject<span class="token operator">/</span><span class="token keyword">instance</span>
from app <span class="token keyword">import</span> db
from app <span class="token keyword">import</span> User
user_all <span class="token operator">=</span> User<span class="token punctuation">.</span>query<span class="token punctuation">.</span><span class="token function">all</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
user_all
<span class="token punctuation">[</span><span class="token operator">&lt;</span>User <span class="token number">1</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">8</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">9</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">10</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">11</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">12</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">13</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">14</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">15</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">16</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">17</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">18</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">20</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">21</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">22</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">23</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">24</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">29</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">30</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">31</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">32</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">34</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">35</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">42</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">53</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">57</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">66</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">70</span><span class="token operator">></span><span class="token punctuation">,</span> <span class="token operator">&lt;</span>User <span class="token number">71</span><span class="token operator">></span><span class="token punctuation">]</span>
<span class="token function">len</span><span class="token punctuation">(</span>user_all<span class="token punctuation">)</span>
<span class="token number">29</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
#### b.按条件查询

<ul>
<li><p><code>filter_by</code></p>
<div class="code-wrapper"><pre class="line-numbers language-shell" data-language="shell"><code class="language-shell">user_age &#x3D; User.query.filter_by(age &#x3D; 40).first()
user_age
&lt;User 66&gt;
user_age.username
&#39;e&#39;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></li>
<li><p><code>filter</code></p>
<div class="code-wrapper"><pre class="line-numbers language-shell" data-language="shell"><code class="language-shell">user &#x3D; User.query.filter(User.username.endswith(&#39;n&#39;)).all()
user
[&lt;User 1&gt;] #list
user[0].username
&#39;ethan&#39; 


user &#x3D; User.query.filter(User.age&gt;40).all()
user
[&lt;User 8&gt;, &lt;User 10&gt;, &lt;User 12&gt;, &lt;User 24&gt;, &lt;User 29&gt;]
for i in user:
    print(i.username,i.age)
    
i 43
r 43
w 41
# 49
s 41<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></li>
<li><p><code>order_by</code></p>
<div class="code-wrapper"><pre class="line-numbers language-shell" data-language="shell"><code class="language-shell">user &#x3D; User.query.order_by(User.age)
user
&lt;flask_sqlalchemy.BaseQuery object at 0x7fb612334820&gt;
user.all()
[&lt;User 20&gt;, &lt;User 32&gt;, &lt;User 11&gt;, &lt;User 22&gt;, &lt;User 1&gt;, &lt;User 9&gt;, &lt;User 16&gt;, &lt;User 23&gt;, &lt;User 53&gt;, &lt;User 15&gt;, &lt;User 13&gt;, &lt;User 14&gt;, &lt;User 71&gt;, &lt;User 17&gt;, &lt;User 18&gt;, &lt;User 42&gt;, &lt;User 31&gt;, &lt;User 70&gt;, &lt;User 21&gt;, &lt;User 30&gt;, &lt;User 57&gt;, &lt;User 35&gt;, &lt;User 34&gt;, &lt;User 66&gt;, &lt;User 12&gt;, &lt;User 29&gt;, &lt;User 8&gt;, &lt;User 10&gt;, &lt;User 24&gt;]
for i in user.all():
    print(i.age)
    
1
2
3
6
13
14
14
14
14
15
20
22
23
25
26
26
28
28
30
32
33
34
37
40
41
41
43
43
49<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></li>
<li><p><code>limit</code></p>
<div class="code-wrapper"><pre class="line-numbers language-shell" data-language="shell"><code class="language-shell">user &#x3D; User.query.order_by(User.age).limit(10)
user.all()
[&lt;User 20&gt;, &lt;User 32&gt;, &lt;User 11&gt;, &lt;User 22&gt;, &lt;User 1&gt;, &lt;User 9&gt;, &lt;User 16&gt;, &lt;User 23&gt;, &lt;User 53&gt;, &lt;User 15&gt;]
len(user.all())
10<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></li>
<li><p><code>get(1)</code> 根据主键查询</p>
</li>
<li><p><code>query.first()</code>第一条记录</p>
</li>
<li><p><code>first_or_4o4()</code> 有返回无则404</p>
</li>
<li><p><code>get_or_404(1)</code>有返回无则404</p>
</li>
</ul>
<h4 id="c-分页查询"><a href="#c-分页查询" class="headerlink" title="c.分页查询"></a>c.分页查询</h4><ul>
<li><p><code>offset</code> + <code>limit</code></p>
<p><code>.offset(offset).limit(limit)</code></p>
</li>
<li><p><code>paginate</code></p>
<p><code>.paginate(page=2,pre_page=10)</code> 返回<code>Pagination</code>对象</p>
<ul>
<li><code>has_prev/has_next</code> - 是否有上一页或者下一页</li>
<li><code>items</code> - 当前页的数据列表</li>
<li><code>prev_num/next_num</code>-上一页，下一页的页码</li>
<li><code>total</code>-总数</li>
<li><code>pages</code> - 总页数</li>
</ul>
</li>
</ul>
<h2 id="3-数据库模型设计"><a href="#3-数据库模型设计" class="headerlink" title="3 数据库模型设计"></a>3 数据库模型设计</h2><blockquote>
<p>Mysql关系型数据库设计中存在着三种关系：一对一，一对多，多对多.</p>
</blockquote>
<ul>
<li><p>一对一： 表中的一列对应着另外表中一列</p>
</li>
<li><p>一对多：表中的一列对应另外表中的几列</p>
<blockquote>
<p>这种关系在关系型列表中最常见。在旅游表格中，城市列表中的城市可以对应顾客信息表中，顾客的各种信息，联系方式，年龄地址等。</p>
</blockquote>
</li>
<li><p>多对一：表中的多列对应另外表中的多列</p>
<blockquote>
<p>可以将多对多关系视为通过中间表连接的两个一对多关系。</p>
<p>多个表互相一对一。</p>
</blockquote>
</li>
</ul>
<p>我们常用一对多，多对多(多个一对多)</p>
<p>所以我们举例都按照一对多的案例记笔记</p>
<h3 id="3-1-外键关联-foreign-key"><a href="#3-1-外键关联-foreign-key" class="headerlink" title="3.1 外键关联 foreign key"></a>3.1 外键关联 <code>foreign key</code></h3><p>创建一个新的 <code>UserSchool</code>表，来表示学生所属的学校，一个<code>user</code>有一个<code>UserSchool</code></p>
<blockquote>
<p>晚些配合 表单操作一起写</p>
</blockquote>

            </div>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/categories/python/">python</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/tags/flask-orm/">flask-orm</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">本博客所有文章除特别声明外，均采用 <a target="_blank" href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！</p>
              
              
                <div class="post-prevnext">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/posts/34916/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">工具-使用`Mitmproxy`抓包工具抓取移动端数据包</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/posts/26347/">
                        <span class="hidden-mobile">喜欢的壁纸，持续置顶更新呢</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
              <!-- Comments -->
              <article class="comments" id="comments">
                
                
  <div id="vcomments"></div>
  <script type="text/javascript">
    Fluid.utils.waitElementVisible('vcomments', function() {
      Fluid.utils.createScript('https://cdn.jsdelivr.net/npm/valine@1.4.14/dist/Valine.min.js', function () {
        new Valine({
          el: "#vcomments",
          app_id: "Mi65hxq7VAFUDwOLeIGAOgiV-gzGzoHsz",
          app_key: "hMuhiD4FRqhns4giqLiEH9HG",
          placeholder: "说点什么",
          path: window.location.pathname,
          avatar: "retro",
          meta: ["nick","mail","link"],
          pageSize: "10",
          lang: "zh-CN",
          highlight: false,
          recordIP: false,
          serverURLs: "",
        });
      });
    });
  </script>
  <noscript>Please enable JavaScript to view the
    <a target="_blank" href="https://valine.js.org" rel="nofollow noopener noopener">comments powered by Valine.</a>
  </noscript>


              </article>
            
          </article>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div class="toc-body" id="toc-body"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    

    
      <a id="scroll-top-button" href="#" role="button">
        <i class="iconfont icon-arrowup" aria-hidden="true"></i>
      </a>
    

    
      <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
    

    
  </main>

  <footer class="text-center mt-5 py-3">
  <div class="footer-content">
    

  </div>
  
  <div class="statistics">
    
    

    
      
        <!-- 不蒜子统计PV -->
        <span id="busuanzi_container_site_pv" style="display: none">
            总访问量 
            <span id="busuanzi_value_site_pv"></span>
             次
          </span>
      
      
        <!-- 不蒜子统计UV -->
        <span id="busuanzi_container_site_uv" style="display: none">
            总访客数 
            <span id="busuanzi_value_site_uv"></span>
             人
          </span>
      
    
  </div>


  
  <!-- 备案信息 -->
  <div class="beian">
    <span>
      <a href="http://beian.miit.gov.cn/" target="_blank" rel="nofollow noopener">
        豫ICP备20009912号-1
      </a>
    </span>
    
  </div>


  
</footer>

<!-- SCRIPTS -->

  <script  src="https://cdn.jsdelivr.net/npm/nprogress@0.2.0/nprogress.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/nprogress@0.2.0/nprogress.min.css" />

  <script>
    NProgress.configure({"showSpinner":true,"trickleSpeed":100})
    NProgress.start()
    window.addEventListener('load', function() {
      NProgress.done();
    })
  </script>


<script  src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.min.js" ></script>
<script  src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/js/bootstrap.min.js" ></script>
<script  src="/js/debouncer.js" ></script>
<script  src="/js/events.js" ></script>
<script  src="/js/plugins.js" ></script>

<!-- Plugins -->


  
    
      <script  src="/js/lazyload.js" ></script>
    
  



  
    
  



  <script  src="https://cdn.jsdelivr.net/npm/tocbot@4.12.0/dist/tocbot.min.js" ></script>



  <script  src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.5.7/dist/jquery.fancybox.min.js" ></script>



  <script  src="https://cdn.jsdelivr.net/npm/anchor-js@4.3.0/anchor.min.js" ></script>



  <script defer src="https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js" ></script>



  <script defer src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" ></script>




  <script  src="https://cdn.jsdelivr.net/npm/typed.js@2.0.11/lib/typed.min.js" ></script>
  <script>
    (function (window, document) {
      var typing = Fluid.plugins.typing;
      var title = document.getElementById('subtitle').title;
      
      typing(title)
      
    })(window, document);
  </script>



  <script  src="/js/local-search.js" ></script>
  <script>
    (function () {
      var path = "http://qiniuyun.quancundexiwang.wang/xmlSearch.xml";
      var inputArea = document.querySelector("#local-search-input");
      inputArea.onclick = function () {
        searchFunc(path, 'local-search-input', 'local-search-result');
        this.onclick = null
      }
    })()
  </script>















<!-- 主题的启动项 保持在最底部 -->
<script  src="/js/boot.js" ></script>



</body>
</html>
